package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.RoomType;
import com.wanmait.hotelManage.vo.RoomTypeComment;
import com.wanmait.hotelManage.vo.RoomTypeCommentReport;
import com.wanmait.hotelManage.vo.Userinfo;

public class RoomTypeCommentDAO {
	private RoomTypeCommentDAO() {};
	private static RoomTypeCommentDAO roomtypecommentdao=new RoomTypeCommentDAO();
	public static RoomTypeCommentDAO getRoomtypecommentdao() {
		return roomtypecommentdao;
	}
	/**
	 * 评论审核表的初始化之一
	 * @return
	 */
	public List<RoomTypeComment>findAllFromRoomTypeCommentReport(Pager pager){
		List<RoomTypeComment>RoomTypeComments=new ArrayList<RoomTypeComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT room_type_comment.id,room_type_comment.userinfo_id,room_type_comment.content,room_type_comment.input_time,userinfo.username,userinfo.pic ");
		sb.append(" FROM room_type_comment,room_type_comment_report,userinfo ");
		sb.append(" WHERE room_type_comment.id=room_type_comment_report.room_type_comment_id AND room_type_comment.userinfo_id=userinfo.id  AND room_type_comment_report.status=0 AND room_type_comment.enable=1  ORDER by room_type_comment_report.input_time desc limit ?,? ");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			RoomTypeComment rtc=null;
			Userinfo ui=null;
			while(rs.next()) {
				rtc=new RoomTypeComment();
				rtc.setId(rs.getInt("room_type_comment.id"));
				rtc.setContent(rs.getString("room_type_comment.content"));
				rtc.setInputTime(rs.getTimestamp("room_type_comment.input_time"));
				ui=new Userinfo();
				ui.setId(rs.getInt("room_type_comment.userinfo_id"));
				ui.setUsername(rs.getString("userinfo.username"));
				ui.setPic(rs.getString("userinfo.pic"));
				rtc.setUserinfo(ui);
				RoomTypeComments.add(rtc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return RoomTypeComments;
	}
//	public static void main(String[] args) {
//		RoomType roomType=new RoomType();
//		roomType.setId(1);
//		int sum=RoomTypeCommentDAO.getRoomtypecommentdao().getsum(roomType);
//		System.out.println(sum);
//	}
//	public int getrows(){
//		Connection conn=null;
//		PreparedStatement ps=null;
//		ResultSet rs=null;
//		StringBuilder sb=new StringBuilder();
//		sb.append(" select count(*) c from room_type_comment_report,room_type_comment,userinfo where room_type_comment.id=room_type_comment_report.room_type_comment_id ");
//		sb.append(" AND room_type_comment.userinfo_id=userinfo.id  AND room_type_comment_report.status=0 AND room_type_comment_report.enable=0 ");
//		conn=DBHelper.getDBHelper().getConn();
//		try {
//			ps=conn.prepareStatement(sb.toString());
//			rs=ps.executeQuery();
//			rs.next();
//			Integer count=rs.getInt("c");
//			return count;
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			DBHelper.getDBHelper().close(rs, ps, conn);
//		}
//		return 0;
//	}
	/**
	 * 评论举报的通过
	 * @param parseInt
	 */
	public void updateby(int parseInt) {
		String sql="update room_type_comment set enable=0 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, parseInt);
	}
	/**
	 * 评论举报的批量通过
	 * @param id1
	 */
	public void updatebys(String id1) {
		String sql="update room_type_comment set enable=0 where id in("+id1+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	/**
	 * 评论下的所有自集
	 * @param roomType
	 * @param roomTypeComment
	 */
//	public void chlidren(RoomType roomType,RoomTypeComment roomTypeComment){
//		ArrayList<RoomTypeComment>children=new ArrayList<RoomTypeComment>();
//		Connection conn=null;
//		PreparedStatement ps=null;
//		ResultSet rs=null;
//		StringBuilder sb=new StringBuilder();
//		sb.append(" SELECT * FROM room_type_comment,userinfo,room_type WHERE room_type_comment.userinfo_id=userinfo.id ");
//		sb.append(" AND room_type_comment.room_type_id=? AND room_type_comment.parent_id=?  AND room_type.id=room_type_comment.room_type_id");
//		conn=DBHelper.getDBHelper().getConn();
//		try {
//			ps=conn.prepareStatement(sb.toString());
//			ps.setInt(1,roomType.getId());
//			ps.setInt(2, roomTypeComment.getId());
//			rs=ps.executeQuery();
//			RoomType rt=null;
//			Userinfo ui=null;
//			while(rs.next()) {
//				RoomTypeComment rtc=new RoomTypeComment();
//				rtc.setId(rs.getInt("room_type_comment.id"));
//				rtc.setContent(rs.getString("room_type_comment.content"));
//				rtc.setInputTime(rs.getTimestamp("room_type_comment.input_time"));
//				rt=new RoomType();
//				rt.setId(rs.getInt("room_type.id"));
//				rt.setTitle(rs.getString("room_type.title"));
//				rtc.setRoomtype(rt);
//				ui=new Userinfo();
//				ui.setId(rs.getInt("userinfo.id"));
//				ui.setUsername(rs.getString("userinfo.username"));
//				rtc.setUserinfo(ui);
//				children.add(rtc);
//				this.chlidren(roomType, rtc);
//			}
//			roomTypeComment.setChildren(children);
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			DBHelper.getDBHelper().close(rs, ps, conn);
//		}
//	}
	public ArrayList<RoomTypeComment>getfindall(RoomType roomType){
		ArrayList<RoomTypeComment>roomTypeComments=new ArrayList<RoomTypeComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT * FROM room_type_comment,userinfo,room_type WHERE room_type_comment.userinfo_id=userinfo.id ");
		sb.append(" AND room_type_comment.room_type_id=? AND room_type_comment.parent_id=0  AND room_type.id=room_type_comment.room_type_id and room_type_comment.enable=1");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1,roomType.getId());
			rs=ps.executeQuery();
			RoomType rt=null;
			Userinfo ui=null;
			while(rs.next()) {
				RoomTypeComment rtc=new RoomTypeComment();
				rtc.setId(rs.getInt("room_type_comment.id"));
				rtc.setContent(rs.getString("room_type_comment.content"));
				rtc.setInputTime(rs.getTimestamp("room_type_comment.input_time"));
				rt=new RoomType();
				rt.setId(rs.getInt("room_type.id"));
				rt.setTitle(rs.getString("room_type.title"));
				rtc.setRoomtype(rt);
				ui=new Userinfo();
				ui.setId(rs.getInt("userinfo.id"));
				ui.setUsername(rs.getString("userinfo.username"));
				ui.setPic(rs.getString("userinfo.pic"));
				rtc.setUserinfo(ui);
//				this.chlidren(roomType, rtc);
				roomTypeComments.add(rtc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return roomTypeComments;
	}
	public int getsum(RoomType roomType) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT count(*) c FROM room_type_comment,userinfo,room_type WHERE room_type_comment.userinfo_id=userinfo.id ");
		sb.append(" AND room_type_comment.room_type_id=? AND room_type_comment.parent_id=0  AND room_type.id=room_type_comment.room_type_id and room_type_comment.enable=1");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1,roomType.getId());
			rs=ps.executeQuery();
			if(rs.next()) {
				int sum=rs.getInt("c");
				return sum;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	public List<RoomTypeComment> findid1(String id1, Pager pager) {
		List<RoomTypeComment>rtcrs=new ArrayList<RoomTypeComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		 sb.append(" SELECT room_type_comment.id,room_type_comment.userinfo_id,room_type_comment.content,room_type_comment.input_time,userinfo.username,userinfo.pic ");
		 sb.append(" FROM room_type_comment,room_type_comment_report,userinfo ");
		 sb.append(" WHERE room_type_comment.id=room_type_comment_report.room_type_comment_id AND room_type_comment_report.userinfo_id IN ("+id1+") ");
		 sb.append(" and room_type_comment.userinfo_id=userinfo.id AND room_type_comment_report.status=0 AND room_type_comment.enable=1  ORDER by room_type_comment_report.input_time desc LIMIT ?,? ");
		 conn=DBHelper.getDBHelper().getConn();
		 try {
			ps=conn.prepareStatement(sb.toString());
			 ps.setInt(1, pager.getStart());
			 ps.setInt(2, pager.getPageSize());
			 rs=ps.executeQuery();
			 Userinfo ui=null;
			 while(rs.next()) {
				 RoomTypeComment rtc=new RoomTypeComment();
					rtc.setId(rs.getInt("room_type_comment.id"));
					rtc.setContent(rs.getString("room_type_comment.content"));
					rtc.setInputTime(rs.getTimestamp("room_type_comment.input_time"));
					ui=new Userinfo();
					ui.setId(rs.getInt("room_type_comment.userinfo_id"));
					ui.setUsername(rs.getString("userinfo.username"));
					ui.setPic(rs.getString("userinfo.pic"));
					rtc.setUserinfo(ui);
					rtcrs.add(rtc);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return rtcrs;
	}
}
